/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package schedulerManagerment.SkillFieldManager.jdbc;

import connection.Configuration;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import schedulerManagerment.SkillFieldManager.entities.Skill_info;

/**
 *
 * @author tuanIT
 */
public class SkillList {
    private ArrayList<Skill_info> sList;
    private Configuration conf;

    public ArrayList<Skill_info> getsList() {
        return sList;
    }

    public void setsList(ArrayList<Skill_info> sList) {
        this.sList = sList;
    }
    
    private void getInfomation() {
        try {
            this.conf = new Configuration();
            Connection conn = conf.getConnect();
            
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("select * from Skill");
            while (rs.next()) {
                
               Skill_info s = new Skill_info();
                
               s.setsID(rs.getInt("ID"));
               s.setsName(rs.getString("name"));
               
               sList.add(s);
            }
        } catch (SQLException ex) {
            Logger.getLogger(FieldList.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public SkillList() {
        this.sList = new ArrayList<Skill_info>();
        getInfomation();
    }
    public void refresh(){
        sList.clear();
        getInfomation();
    }
    
    public Skill_info getById(int id)
     {
         for(Skill_info s : sList)
         {
             if(s.getsID() == id)
             {
                 return s;
             }
         }
        return null;
     }
      public boolean insert(String name){
        try {
             this.conf = new Configuration();
            Connection conn = conf.getConnect();
            String q = "select * from Skill";
            Statement st1 = conn.createStatement();
            ResultSet rs = st1.executeQuery(q);
            String qry = "insert into Skill values ('"+name+"')";
            rs.isBeforeFirst();
            while (rs.next()) {
                if (name.equals(rs.getString("name"))) {
                    JOptionPane.showMessageDialog(null, "This skill already exits!");
                    
                    break;
                }
            }
            if (rs.isAfterLast()) {
                //String qry = "insert into Field values ('" + name + "')";
                Statement st = conn.createStatement();
                st.execute(qry);
                refresh();
            }
        } catch (SQLException ex) {
            Logger.getLogger(FieldList.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        return true;
    }
      
      public boolean update(int id,String name){
        try {
            this.conf = new Configuration();
            Connection conn = conf.getConnect();
            String q = "select * from Skill";
            Statement st1 = conn.createStatement();
            ResultSet rs = st1.executeQuery(q);
            String qry = "update Skill set name = '"+name+"' where ID="+id;
            rs.isBeforeFirst();
            while (rs.next()) {
                if (name.equals(rs.getString("name"))) {
                    JOptionPane.showMessageDialog(null, "This skill already exits!");
                    
                    break;
                }
            }
            if (rs.isAfterLast()) {
                
                Statement st = conn.createStatement();
                st.execute(qry);
                refresh();
            }
        } catch (SQLException ex) {
            Logger.getLogger(FieldList.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        return true;
    }
      
      public boolean delete(int id)
      {
          try {
            String qry = "delete Skill where ID="+id;
            this.conf = new Configuration();
            Connection conn = conf.getConnect();
            Statement st = conn.createStatement();
            st.executeUpdate(qry);
            refresh();
        } catch (SQLException ex) {
            Logger.getLogger(FieldList.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        return true;
      }
    
}
